﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Threading.Tasks;
using DataTier;
using System.Data;

namespace DataAccessLayer
{
    public class DBSubscription
    {
        private static SqlCommand dbCmd = null;

        public static List<Subscription> GetSubscriptions(int cId)
        {
            List<Subscription> returnList = new List<Subscription>();
            string sql = "select * "
                       + "from Customer C, Subscription S, SubscriptionType ST, CarModel CM, Battery B "
                       + "where C.cId = S.cId "
                       + "and S.stId = ST.stId "
                       + "and S.cmId = CM.cmId "
                       + "and B.batteryId = S.batteryId "
                       + "and C.cId = " + cId;
            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = dbCmd.ExecuteReader();

            Subscription s;

            while (dbReader.Read())
            {
                s = new Subscription((int)(dbReader["sId"]), (int)(dbReader["stId"]), (int)(dbReader["carId"]), (int)(dbReader["batteryId"]), (int)(dbReader["cId"]), (int)(dbReader["cmId"]));
                returnList.Add(s);
            }

            AccessDBSQLClient.Close();

            foreach (Subscription subs in returnList)
            {

              subs.Battery =  DBBattery.GetBattery(subs.Battery.BatteryId);
               
              subs.CarModel =  DBBattery.GetCarModel(subs.CarModel.CarModelId);

              //subs.SubscriptionType = DBSubscription.GetSubType(subs.SubscriptionType.Id);
                 
            }
           

            return returnList;
        }

        public static SubscriptionType GetSubType(int subId)
        {
            string sql = @"select * from Subscription s, SubscriptionType st where s.stId = st.stId and s.sId = '" + subId + "'";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = dbCmd.ExecuteReader();

            SubscriptionType st;

            if (dbReader.Read())
                st = new SubscriptionType(dbReader["stName"].ToString(), dbReader["stDescription"].ToString(), (double)dbReader["stPrice"], (double)dbReader["stSetupCost"]);
            else
                st = null;
            dbReader.Close();
            AccessDBSQLClient.Close();
            return st;
        }

        public static Subscription getSubscription(int sId)
        {
            string sql = @"select * from Subscription where sId = '" + sId + "'";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = dbCmd.ExecuteReader();

            Subscription s;

            if (dbReader.Read())
                s = new Subscription((int)(dbReader["sId"]), (int)(dbReader["cId"]));
            else
            {
                s = null;
            }
            dbReader.Close();
            AccessDBSQLClient.Close();

            s.Customer = DBCustomer.FindCustomerByNo(1);

            return s;
        }
        

        public static void CreateSubscription(int bmId, int subTypeId, int carId, int customerId, int carModelId)
        {

            DBBattery.CreateBattery(bmId);   
            string sql1 = @"select Max(batteryId) as battery from Battery where stationId is NULL";
            dbCmd = AccessDBSQLClient.GetDbCommand(sql1);

            IDataReader dbReader;
            dbReader = dbCmd.ExecuteReader();

           if (dbReader.Read())
         {
                int d = int.Parse(dbReader["battery"].ToString());

                string sql = @"insert into Subscription values ('" + subTypeId + "','" + carId + "','" + d + "','" + customerId + "','" + carModelId + "')";

                SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
                dbReader.Close();
                cmd.ExecuteNonQuery();
                AccessDBSQLClient.Close();
            }
        }

        public static void DeleteSubscription(int subId)
        {
            string sql = @"delete from Subscription where sId ='" + subId + "'";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();
        }

        public static void UpdateSubscription(int subId, int subTypeId, int carId, int batteryId, int customerId, int carModelId)
        {
            string sql = @"update Subscription set stId = '"+subTypeId+"', carId = '" +carId+"', batteryId = '"+batteryId+"', cId = '"+customerId+"', cmId ='"+carModelId+"' where sId ='"+subId+"'";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();
        }

        public static List<Battery> GetBatteriesInSubscription(int subscriptionId)
        {
            List<Battery> returnList = new List<Battery>();

            string sql = @"select * from Subscription where sId =  '" + subscriptionId + "'";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Battery b;

            while (dbReader.Read())
            {
                b = new Battery((int)(dbReader["batteryId"]));

                returnList.Add(b);
            }
            AccessDBSQLClient.Close();

            return returnList;

        }
    }
}